from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_sqlalchemy.query import Query
from flask_migrate import Migrate  # 动态更新模型对应的表结构


app: Flask = Flask(__name__)

HOSTNAME = "127.0.0.1"
PORT = 3306
USERNAME = "root"
PASSWORD = "Yan1029."
DATABASE = "flask_study"

app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8mb4"

db: SQLAlchemy = SQLAlchemy(app)

migrate: Migrate = Migrate(app, db)


class User(db.Model):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(100), nullable=False)
    password = db.Column(db.String(100), nullable=False)
    # articles = db.relationship("Article", back_populates="author")  # (一对多，一个作者有多篇文章)双向绑定, 将来 user.articles 时会去访问 Article 对象中的 author 属性

    def __str__(self):
        return f'User({self.id, self.username, self.password})'


class Article(db.Model):
    __tablename__ = "article"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    author_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    # author = db.relationship("User", back_populates="articles")  # 直接通过 article.author 即可自动读取到 user 信息(一对一,一篇文章只对应一个作者)
    # 等价于下面这种写法，只不过下面这种写法不需要显式给 User 对象添加 articles 属性
    author = db.relationship("User", backref="articles")  # 直接通过 article.author 即可自动读取到 user 信息(一对一,一篇文章只对应一个作者)
    title = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)

    def __str__(self):
        return f'Article({self.author}, {self.title})'


# 创建模型对应的表
# 方式1
# with app.app_context():
#     db.create_all()  # 如果表存在则不会重复创建(但是如果对表模型进行修改的话 这里并不会更新表结构，这也是create_all的局限性)
# 方式2 (命令行执行,主程序名称必须是 app1.py ！) 如果没有 flask 环境就执行 python -m flask db xxx
# todo 1. flask db init (只需要执行一次)
# todo 2. flask db migrate (模型更新后都需要执行)
# todo 3. flask db upgrade (模型更新后都需要执行)


@app.route("/user/add")
def add_user():
    user: User = User(username="李大喜", password="123456")
    db.session.add(user)
    db.session.commit()
    return '用户添加成功'


@app.route("/article/add")
def add_article():
    article: Article = Article(author_id=1, title="Why Python is the best language in this world?", content="Hello World")
    db.session.add(article)
    db.session.commit()
    return '文章添加成功'


@app.route("/article")
def query_article():
    article: Article = Article.query.filter(Article.id < 10).first()
    return f'作者信息: {article.author}'


@app.route("/user/articles")
def query_articles_by_user():
    user: User = User.query.filter_by(username="李大喜").first()
    articles = user.articles
    result:str = ''
    for article in articles:
        result = result + str(article)
    return f'articles: {result}'


# 根据主键查找
@app.route("/user")
def query_user():
    user = User.query.get(1)
    return '%s' % user


@app.route("/users")
def query_users():
    # 1. filter
    users: Query = User.query.filter_by(username="李大喜")
    # users: Query = User.query.filter(User.username == "李大喜")

    # 2. order by
    users: Query = User.query.order_by("id")
    # users: Query = User.query.order_by(User.id)
    users: Query = User.query.order_by(db.text("-id"))
    # users: Query = User.query.order_by(User.id.desc())

    # 3. group by
    from sqlalchemy import func
    users = db.session.query(User.username, func.count(User.id)).group_by("username").all()

    # 4. like
    users = User.query.filter(User.username.contains("李"))
    # users = User.query.filter(User.username.like("%李%"))

    # 5. in
    users = User.query.filter(User.username.in_(['张三','李四']))

    # 6. not in
    users = User.query.filter(~User.username_in(['张三','李四']))

    # 7. is null
    users = User.query.filter(User.username is None)
    # users = User.query.filter(User.username.is_(None))

    # 8. is not null
    users = User.query.filter(User.username is not None)
    # users = User.query.filter(User.username.isnot(None))

    # 9. and / or
    from sqlalchemy import and_, or_
    users = User.query.filter(and_(User.username == '张三', User.id < 10))
    users = User.query.filter(or_(User.username == '张三', User.id < 10))

    return '%s' % users[0]


@app.route("/user/update")
def user_update():
    user: User = User.query.filter_by(username="李大喜").first()
    user.password = "123123"
    db.session.commit()
    return "修改成功"


@app.route("/user/del")
def user_del():
    # 删除单条数据
    user: User = User.query.get(1)
    db.session.delete(user)
    # 删除多条数据
    User.query.filter(User.username.contains("李")).delete(synchronize_session=False)
    db.session.commit()


if __name__ == '__main__':
    app.run(debug=True)


